#coding=utf-8
from sqlalchemy import create_engine
from sqlalchemy.sql import func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey,Column,String,Integer,\
                       Boolean,Date,BLOB,Text,Float,DateTime
#engine = create_engine("mysql+pymysql://root:5813@localhost:3306/testdb?charset=utf8",\
#                      pool_size=10,max_overflow=3,echo_pool=True)
# engine = create_engine("mysql+pymysql://shensy:shensy@39.98.207.118:3306/zxzl_dev?charset=utf8",\
#                       pool_size=10,max_overflow=3,echo_pool=True)
engine = create_engine("mysql+pymysql://root:9999@localhost/zxzl?charset=utf8",
                       pool_size=10,max_overflow=3,echo_pool=True)
Base = declarative_base()  #orm基类

#用户表
class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True)
    username = Column(String(50))
    passwd = Column(String(60))
    tel = Column(String(11),unique=True)
    is_valid = Column(Boolean)

#角色表
class Role(Base):
    __tablename__ = 'role'
    id = Column(Integer,primary_key=True)
    role = Column(String(1),unique=True)
    name = Column(String(40))

#设备表
class Dev(Base):
    __tablename__ = 'dev'
    id = Column(Integer,primary_key=True)
    deviceSN = Column(String(40),unique=True)
    cpuid = Column(String(40),unique=True)
    prod_time = Column(Date)
    status = Column(String(1))

#客户表
class Guest(Base):      # <<<<<<<<<<<<<<有较大改动
    __tablename__ = 'guest'
    id = Column(Integer, primary_key=True)
    company_name = Column(String(60))
    contract_name = Column(String(20))
    contact_tel = Column(String(11))
    tax_payer = Column(String(40), unique=True)
    legal_name = Column(String(20))
    legal_tel = Column(String(11))
    company_addr = Column(String(100))

#用户设备表
class UserLinkDev(Base):
    __tablename__ = 'user_link_dev'
    id = Column(Integer,primary_key=True)
    user_id = Column(ForeignKey('user.id'))
    device_id  = Column(ForeignKey('dev.id'))


#用户角色关联表
class UserLinkRole(Base):
    __tablename__ = 'user_link_role'
    id = Column(Integer, primary_key=True)
    user_id = Column(ForeignKey('user.id'))
    role_id = Column(ForeignKey('role.id'))
    company_name = Column(ForeignKey('guest.id'))       # <<<<<<<<改成company_id


#后端url表
class BackendUrl(Base):
    __tablename__ =  'backend_url'
    id = Column(Integer, primary_key=True)
    url = Column(String(100))
    url_name = Column(String(40))

#前端页面表
class FrontendPage(Base):
    __tablename__ = 'frontend_page'
    id = Column(Integer, primary_key=True)
    #path = Column(Integer)
    name = Column(String(20))
    #component = Column(String(40))
    parent_id = Column(String(40))
    child_id = Column(String(500))

#角色后端关联表
class RoleLinkBackend(Base):
    __tablename__ = 'role_link_backend'
    id = Column(Integer,primary_key=True)
    role_id = Column(ForeignKey('role.id'))
    url_id = Column(ForeignKey('backend_url.id'))

#角色前端页面关联表
class RoleLinkFrontpage(Base):
    __tablename__ = 'role_link_frontpage'
    id = Column(Integer,primary_key=True)
    role_id = Column(ForeignKey('role.id'))
    url_id = Column(ForeignKey('frontend_page.id'))

#资源表
class Resource(Base):
    __tablename__ = 'resource'
    id = Column(Integer,primary_key=True)
    resource = Column(String(100))
    #resource_name = Column(String(20))
    parent_id = Column(String(40))
    child_id = Column(String(500))
    page_id = Column(ForeignKey('frontend_page.id'))

#角色资源关联表
class RoleLinkResource(Base):
    __tablename__ = 'role_link_resource'
    id = Column(Integer,primary_key=True)
    role_id = Column(ForeignKey('role.id'))
    resource_id = Column(ForeignKey('resource.id'))

#已建猪场纪录表
class FarmRecord(Base):
    __tablename__ = 'farm_record'
    id = Column(Integer,primary_key=True)
    company_name = Column(ForeignKey('guest.id'))       # <<<<<<<<<<<改成company_id
    farm_name = Column(String(60))
    pigsty_name = Column(String(60))
    swinery_name = Column(String(60))
    average_age = Column(Integer)
    feed_num = Column(Integer)
    batch = Column(Integer)

#设备参数表
class DevParam(Base):   # <<<<<<<<<<<<<有较大改动
    __tablename__ = 'dev_param'
    id = Column(Integer,primary_key=True)
    device_id = Column(ForeignKey('dev.id'))
    wat_break = Column(Boolean)
    wat_flow_lo = Column(Boolean) 
    wat_flow_hi = Column(Boolean)
    moto_temp_hi = Column(Boolean)
    feeder_empt = Column(Boolean)
    ultr_sens_dis = Column(Boolean)
    flow_sens_dis = Column(Boolean)
    str_line_dis = Column(Boolean)
    wat_val_dis = Column(Boolean)
    online_stat = Column(Boolean)
    act_stat = Column(Boolean)
    duetime_prom = Column(Integer)
    vacant_time = Column(Float)
    trig_num = Column(Float)
    trig_interval = Column(Float)
    cali_quan = Column(Float)
    wat_fer_rate = Column(Float)
    feed_line_ctrl = Column(Boolean)
    weight_per_circ = Column(Float)
    vib_stat = Column(Boolean)
    vib_amp = Column(Float)
    pow_par = Column(Boolean)

#设备统计数据表
class DevStatisticsInfo(Base):
    __tablename__ = 'dev_statistics_info'
    id = Column(Integer,primary_key=True)
    device_id = Column(ForeignKey('dev.id'))
    record_time = Column(DateTime(timezone=True),server_default=func.now())
    str_trig_num = Column(Integer)
    ultr_ran_num = Column(Float)
    equi_acc_mat = Column(Float)
    equi_acc_wat = Column(Float)
    
#猪栏设备关联表
class SwineryLinkDev(Base):
    __tablename__ = 'swinery_link_dev'    
    id = Column(Integer,primary_key=True)
    swinery_name = Column(ForeignKey('farm_record.id'))    
    device_id  = Column(ForeignKey('dev.id'))

#合同表
class Contract(Base):
    __tablename__ = 'contract'
    id = Column(Integer,primary_key=True)
    company_name = Column(ForeignKey('guest.id'))       # <<<<<<<<<<没有合同号
    contract_date = Column(Date)
    expiry_time  = Column(Integer)
    purcha_num = Column(Integer)
    legal_person = Column(String(20))
    legal_person_tel = Column(String(11))
    admin_account = Column(String(40))
    dev_activate_data = Column(Date)
    dev_deadline_data = Column(Date)
    status = Column(String(1))

#合同设备关联表
class ContractLinkDev(Base):
    __tablename__ = 'contract_link_dev'
    id = Column(Integer,primary_key=True)
    contract_id = Column(ForeignKey('contract.id'))
    device_id = Column(ForeignKey('dev.id'))
    bind_time = Column(Date)            # <<<<<<<<<<<<<<具体到小时分钟？
    addname = Column(String(20))
    is_valid = Column(Boolean)

#客户合同关联表
class GuestLinkContract(Base):
    __tablename__ = 'guest_link_contract'
    id = Column(Integer,primary_key=True)
    customer_id = Column(ForeignKey('guest.id'))
    contract_id =Column(ForeignKey('contract.id'))

#咨询列表
class News(Base):
    __tablename__ = 'news'
    id = Column(Integer,primary_key=True)
    title = Column(String(100))
    image = Column(BLOB)
    intime = Column(Date)
    content = Column(Text)

#轮播图表
class Carousel(Base):
    __tablename__ = 'carousel'
    id = Column(Integer,primary_key=True)
    image = Column(BLOB)
    name = Column(String(40))

#操作日志表
class OperLogs(Base):
    __tablename__ = 'oper_logs'
    id = Column(Integer,primary_key=True)
    oper_user = Column(ForeignKey('user.id'))
    oper_time = Column(DateTime(timezone=True),server_default=func.now())
    oper_user_company = Column(ForeignKey('guest.id'))
    oper_dev = Column(String(30))
    oper_action = Column(String(4))
    oper_tb = Column(String(20))
    oper_col = Column(String(20))
    bf_oper = Column(String(200))
    af_oper = Column(String(200))

if __name__ == '__main__':
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine) #创建表结构
    from sqlalchemy.orm import sessionmaker
    Session = sessionmaker(bind=engine)
    sess = Session()
    for role,name in [('A','猪场老板'),('B','主场副总'),('C','中层管理(舍长)'),
                      ('D','饲养员'),('G','猪场老板（只读）'),('P','卖家销售'),
                      ('M','卖家管理'),('S','超级管理员'),('Z','游客')]:
        rl = Role(role=role,name=name)
        sess.add(rl)
    sess.commit()

